Using SUM, AVERAGE, COUNT, and DISTINCTCOUNT in Power BI

Course: Microsoft Power BI Training

Topic: DAX Aggregation Functions — SUM, AVERAGE, COUNT, and DISTINCTCOUNT

Course Link: Microsoft Power BI Training at JustAcademy

Demo Link: Register for a Course Demo

Learning Resource: Visit JustAcademy

1. Introduction to DAX Aggregation Functions

Power BI uses Data Analysis Expressions (DAX) to create calculations for reports and semantic models. Aggregation functions summarize multiple rows into a single result, helping users understand sales, costs, quantities, customer activity, and other business metrics.

Four important DAX aggregation functions are SUM, AVERAGE, COUNT, and DISTINCTCOUNT. Each function answers a different type of business question.

• SUM: Adds numeric values across rows.
• AVERAGE: Calculates the arithmetic mean of numeric values.
• COUNT: Counts nonblank values in a supported column, including numeric values and dates.
• DISTINCTCOUNT: Counts the number of distinct values, including a blank value if one exists.

1.1 Why Are Aggregation Functions Important?

• They turn detailed records into useful business summaries.
• They support KPIs, cards, tables, charts, and dashboards.
• They help compare performance across products, regions, and periods.
• They allow report users to analyze metrics using slicers and filters.
• They reduce the need to manually calculate totals and counts.

1.2 Understanding the Example Dataset

Throughout these notes, we will use a Sales table containing the following data:

The dataset contains five sales records, a total sales amount of 137500, a total quantity of 15, and four distinct customer IDs. We will use these values to understand how each function works.

2. Understanding Measures in Power BI

Although DAX aggregation functions can be used in calculated columns or other expressions where appropriate, they are commonly used in measures to create dynamic report calculations.

A measure calculates a result when it is evaluated in a report visual. Its result can change depending on the filters, slicers, rows, columns, and other context affecting that visual.

2.1 How to Create a Measure

1. Open Power BI Desktop and load your dataset.
2. Select the table where you want to organize the measure.
3. Choose New measure from the ribbon or table context menu.
4. Enter the DAX formula in the formula bar.
5. Press Enter to create the measure.
6. Add a Card, Table, Matrix, or Chart visual to test the result.

2.2 Basic Measure Syntax

Measure Name = DAX Expression

For example:

Total Sales = SUM(Sales[Sales Amount])

This measure calculates the total Sales Amount for the current filter context.

3. Using the SUM Function

The SUM function adds all nonblank numeric values in a specified column within the current evaluation context. It is one of the most frequently used DAX aggregation functions.

3.1 Syntax of SUM

SUM(<column>)

The argument must be a numeric column from a table. SUM does not accept an arbitrary expression as its argument; use SUMX when you need to evaluate an expression for each row and then add the results.

3.2 Example: Calculate Total Sales

Total Sales = SUM(Sales[Sales Amount])

Using our sample dataset, the calculation is:

60000 + 36000 + 7500 + 30000 + 4000 = 137500

The Total Sales measure returns 137500 when all five sample records are included in the current filter context.

3.3 Example: Calculate Total Quantity

Total Quantity = SUM(Sales[Quantity])

The total quantity is:

2 + 3 + 5 + 1 + 4 = 15

3.4 Example: Calculate Total Cost

Suppose the Sales table contains a numeric Total Cost column. You can calculate total cost with:

Total Cost = SUM(Sales[Total Cost])

The formula assumes Total Cost already exists and represents the cost amount for each row. If cost must be derived from Quantity multiplied by Unit Cost, first decide whether to create a row-level column or calculate the total using SUMX.

3.5 Using SUM with Filters

Measures respond to filter context. For example, a Product slicer can make Total Sales return only the sales for the selected product, provided the model relationships and filter paths are configured appropriately.

For a measure that always calculates sales for Laptop regardless of the Product filter, you can use CALCULATE:

Laptop Sales =
CALCULATE(
    SUM(Sales[Sales Amount]),
    Sales[Product] = "Laptop"
)

Other filters, such as date and region, can still affect this measure unless explicitly removed or changed. CALCULATE modifies the filter context in which an expression is evaluated.

3.6 When Should You Use SUM?

• Calculating total sales revenue.
• Calculating total order quantities.
• Summing expenses or costs.
• Calculating total profit when row-level profit is already available.
• Summarizing amounts by product, customer, region, or time period.

4. Using the AVERAGE Function

The AVERAGE function calculates the arithmetic mean of the numeric values in a column. It adds the applicable numeric values and divides the sum by the number of values included in the average.

4.1 Syntax of AVERAGE

AVERAGE(<column>)

The argument must be a numeric column. Blank values are generally ignored, while zeros are included in the calculation.

4.2 Example: Calculate Average Sales

Average Sales = AVERAGE(Sales[Sales Amount])

The average for the sample dataset is calculated as follows:

Average Sales = 137500 / 5
Average Sales = 27500

The result is 27500 per sales record because the example contains five nonblank sales amounts.

4.3 Example: Calculate Average Quantity

Average Quantity = AVERAGE(Sales[Quantity])

The total quantity is 15 across five records, so the average quantity per record is 3.

4.4 Example: Calculate Average Sales by Product

When Product is placed on the rows of a table or chart and Average Sales is used as a value, Power BI calculates the average within each product's filter context.

4.5 Understanding Blank Values and Zero

Suppose a numeric column contains 10, 20, 30, and one blank. AVERAGE calculates the mean of the three numeric values, which is 20. If the blank is replaced with zero, the average becomes 15 because zero is included as a numeric value.

This distinction is important when missing values represent unknown information rather than a true value of zero.

4.6 When Should You Use AVERAGE?

• Calculating average order value at the row level when each row represents one order.
• Calculating average quantity per transaction.
• Calculating average product ratings when the rating column is numeric.
• Calculating average delivery time or response time.
• Comparing average sales across regions, products, or periods.

5. Understanding the Arithmetic Mean

The arithmetic mean represents the total of the included numeric observations divided by the number of included observations. It is useful for understanding typical values, but it can be influenced by unusually large or small values.

For example, a few unusually large transactions can increase average sales even if most transactions are small. When analyzing business performance, compare averages with totals, transaction counts, and the distribution of the underlying data.

5.1 Average per Row vs. Average per Customer

Average Sales calculates the mean of sales records. It does not automatically calculate the average amount per unique customer.

To calculate average sales per customer, first decide whether the metric should be based on each customer's total sales, unique customers with sales, or another business definition. The denominator and the aggregation level must match the intended metric.

6. Using the COUNT Function

The COUNT function counts nonblank values in a supported column. In DAX, COUNT supports numeric values, dates, and certain other supported value types. It is not a general-purpose count of all rows regardless of column contents.

6.1 Syntax of COUNT

COUNT(<column>)

6.2 Example: Count Sales Amount Values

Sales Amount Count = COUNT(Sales[Sales Amount])

In our sample dataset, all five Sales Amount values are populated, so the measure returns 5.

6.3 Example: Count Quantity Values

Quantity Count = COUNT(Sales[Quantity])

If all five Quantity values are nonblank numeric values, the result is 5. This counts populated quantity cells, not the total quantity sold. To calculate the total number of units sold, use SUM(Sales[Quantity]).

6.4 COUNT Does Not Mean Count of Rows in Every Situation

If a table contains five rows but only four rows have a nonblank Sales Amount, COUNT(Sales[Sales Amount]) returns 4. It counts the populated values in that column rather than counting every table row.

6.5 COUNT vs. COUNTROWS

Use COUNTROWS when you need to count records regardless of whether a particular column is blank.

6.6 When Should You Use COUNT?

• Counting populated numeric fields.
• Counting populated date values in a supported date column.
• Checking how many records have a value in a particular field.
• Measuring data completeness for supported column types.

7. Using the DISTINCTCOUNT Function

DISTINCTCOUNT counts the number of unique values in a column. Repeated values count once, making it useful for metrics such as unique customers, distinct products, unique orders, or unique transaction identifiers.

7.1 Syntax of DISTINCTCOUNT

DISTINCTCOUNT(<column>)

The argument must be a column. DISTINCTCOUNT includes a blank value as one distinct value when a blank exists in the evaluated column. Use DISTINCTCOUNTNOBLANK when the requirement is to exclude blanks.

7.2 Example: Count Unique Customers

Unique Customers = DISTINCTCOUNT(Sales[Customer ID])

Our sample Customer IDs are C101, C102, C101, C103, and C104. C101 appears twice, but it is counted only once. The result is 4 unique customers.

7.3 Example: Count Unique Products

Unique Products Sold = DISTINCTCOUNT(Sales[Product])

The products are Laptop, Monitor, Keyboard, Laptop, and Mouse. There are four unique product names.

7.4 Example: Count Unique Orders

Unique Orders = DISTINCTCOUNT(Sales[Order ID])

If each order has a unique Order ID and all five IDs are present, the result is 5.

7.5 DISTINCTCOUNT and Blank Values

Suppose a Customer ID column contains C101, C102, C101, and a blank. DISTINCTCOUNT returns 3 because C101, C102, and blank are three distinct values. If blank values should not be included, use:

Unique Customers Excluding Blank =
DISTINCTCOUNTNOBLANK(Sales[Customer ID])

This function excludes blank values from the distinct count. Verify the desired treatment of missing identifiers before choosing the measure.

7.6 When Should You Use DISTINCTCOUNT?

• Counting unique customers.
• Counting distinct order numbers.
• Counting unique products sold.
• Counting distinct employees or account IDs.
• Calculating unique website visitors when the data contains an appropriate visitor identifier.

8. Comparing SUM, AVERAGE, COUNT, and DISTINCTCOUNT

8.1 Choosing the Correct Function

• Ask, "How much in total?" Use SUM for a numeric column.
• Ask, "What is the average value?" Use AVERAGE for a numeric column.
• Ask, "How many populated values are present?" Use COUNT for a supported column type.
• Ask, "How many different values are present?" Use DISTINCTCOUNT.
• Ask, "How many rows are in this table?" Use COUNTROWS.

9. Creating a Sales Summary Dashboard

These four functions can be used together to create a useful sales summary dashboard.

9.1 Create the Measures

Total Sales = SUM(Sales[Sales Amount])

Average Sales = AVERAGE(Sales[Sales Amount])

Sales Amount Count = COUNT(Sales[Sales Amount])

Unique Customers = DISTINCTCOUNT(Sales[Customer ID])

9.2 Add KPI Cards

1. Open Report view.
2. Add a Card visual.
3. Place Total Sales in the card's data field.
4. Repeat for Average Sales, Sales Amount Count, and Unique Customers.
5. Format the sales values as currency if appropriate.
6. Give each card a clear title.

9.3 Add a Product-Level Summary

Create a table or chart with Product and the four measures. Power BI evaluates the measures within each product's filter context, allowing you to compare totals, averages, populated values, and unique customers across products.

9.4 Add Slicers

Add slicers for Order Date, Product, and Region if those fields are available. The measures should update according to the filter context, provided the model relationships and visual interactions are configured correctly.

10. Understanding Filter Context

Filter context is the set of filters applied to a DAX calculation. It can come from slicers, visual rows and columns, page filters, report filters, and expressions that modify context.

10.1 Example: Total Sales by Product

Total Sales = SUM(Sales[Sales Amount])

When Product is placed on the rows of a table visual, the measure calculates total sales separately for each product. When no product filter is applied, it calculates the total across all products included in the current context.

10.2 Example: Unique Customers by Product

Unique Customers = DISTINCTCOUNT(Sales[Customer ID])

This measure counts unique customer IDs for each product. The same customer may buy multiple products, so adding the unique customer counts for individual products may produce a number greater than the overall unique customer count.

10.3 Why Totals May Differ from Adding Visible Rows

DAX measures are recalculated in the total row's filter context. DISTINCTCOUNT is non-additive: the same customer can appear in multiple product groups but should only count once in the overall total. Therefore, the grand total may not equal the sum of the individual product counts.

11. Practical Examples with Business Scenarios

11.1 Retail Sales Analysis

Total Revenue = SUM(Sales[Sales Amount])

Average Transaction Value = AVERAGE(Sales[Sales Amount])

Transaction Value Count = COUNT(Sales[Sales Amount])

Unique Buyers = DISTINCTCOUNT(Sales[Customer ID])

These measures show the revenue total, mean sales-record value, number of populated sales amounts, and number of distinct customer IDs.

11.2 Inventory Analysis

Suppose an Inventory table contains Stock Quantity and Product ID.

Total Stock = SUM(Inventory[Stock Quantity])

Average Stock Per Row = AVERAGE(Inventory[Stock Quantity])

Populated Stock Values = COUNT(Inventory[Stock Quantity])

Distinct Products = DISTINCTCOUNT(Inventory[Product ID])

Average Stock Per Row is an average over inventory records. If a product can have multiple records for warehouses or dates, this is not necessarily the average stock per product. Define the required grain before interpreting the result.

11.3 Employee Analysis

Suppose an Employees table contains Employee ID, Department, and Salary.

Total Salary = SUM(Employees[Salary])

Average Salary = AVERAGE(Employees[Salary])

Populated Salary Count = COUNT(Employees[Salary])

Unique Departments = DISTINCTCOUNT(Employees[Department])

Use these measures to summarize salary data and department diversity. Confirm how missing salaries and blank department values should be handled.

11.4 Website Analytics

Suppose a Website Activity table contains Session ID, Visitor ID, and Page Views.

Total Page Views = SUM(Website Activity[Page Views])

Average Page Views Per Row = AVERAGE(Website Activity[Page Views])

Populated Page View Values = COUNT(Website Activity[Page Views])

Unique Visitors = DISTINCTCOUNT(Website Activity[Visitor ID])

Unique visitor counts depend on the quality and scope of the Visitor ID. If identifiers reset between devices or sessions, the measure may not represent unique people across all devices.

12. Common Mistakes and Troubleshooting

12.1 Check Data Types

SUM and AVERAGE require numeric columns. Ensure the source field is assigned the correct type. For currency, use a suitable numeric data type and currency formatting instead of storing amounts as text.

12.2 Check Blanks and Duplicate Values

Inspect the source data before interpreting COUNT and DISTINCTCOUNT. A blank identifier can affect a distinct count, while duplicate identifiers are expected to be counted only once by DISTINCTCOUNT.

12.3 Check the Intended Grain

Determine what one row represents: a transaction, an order line, a customer, a product, or a daily snapshot. The same aggregation function can answer different business questions depending on the row grain.

13. Performance and Best Practices

• Create reusable measures for frequently used business metrics.
• Use clear names such as Total Sales, Average Sales, and Unique Customers.
• Use numeric data types for numeric calculations.
• Use DISTINCTCOUNT only when uniqueness is required; distinct counting can be more expensive on large datasets than simple sums or counts.
• Use COUNTROWS when you need to count rows rather than populated column values.
• Validate blanks, duplicates, and data quality before interpreting results.
• Use meaningful formats, such as currency, decimal, or whole number.
• Test measures with slicers and different visual groupings.
• Do not assume that averages or distinct counts can be added across groups.
• Document the business definition of each KPI, including its numerator, denominator, and filter assumptions.

14. Practice Project: Create a Sales Performance Report

Build a report that summarizes sales performance for a retail business.

14.1 Requirements

1. Load a Sales table containing Order ID, Customer ID, Product, Quantity, Sales Amount, and Order Date.
2. Create a Total Sales measure using SUM.
3. Create an Average Sales measure using AVERAGE.
4. Create a Sales Amount Count measure using COUNT.
5. Create a Unique Customers measure using DISTINCTCOUNT.
6. Add four Card visuals to display the measures.
7. Add a product-level table showing the measures by Product.
8. Add date and product slicers.
9. Verify that the cards and table respond to filters as expected.
10. Check whether the unique customer grand total differs from the sum of product-level unique customer counts, and explain why.

14.2 Expected Results for the Sample Dataset

15. Practice Exercises

1. Write a measure to calculate the total revenue using SUM.
2. Write a measure to calculate average order-line sales using AVERAGE.
3. Write a measure to count populated Order Date values using COUNT.
4. Write a measure to count unique Product IDs using DISTINCTCOUNT.
5. Explain the difference between COUNT(Sales[Order ID]) and COUNTROWS(Sales).
6. Explain why DISTINCTCOUNT(Sales[Customer ID]) may not equal the sum of customer counts across product categories.
7. Test the effect of blank Customer IDs on a distinct customer count.
8. Create a report with all four measures and filter it by product and date.
9. Determine whether the average sales metric should be per row, per order, or per customer.
10. Document the assumptions and business definitions for each measure.

16. Interview Questions and Answers

Q1. What is the SUM function in DAX?

SUM adds the numeric values in a specified column within the current evaluation context.

Q2. What is the AVERAGE function used for?

AVERAGE calculates the arithmetic mean of the numeric values in a column, generally ignoring blanks and including zeros.

Q3. What does COUNT do in DAX?

COUNT counts nonblank values in a supported column. It is not the same as counting every row in a table.

Q4. What is DISTINCTCOUNT?

DISTINCTCOUNT returns the number of unique values in a column and includes blank as a distinct value if a blank exists.

Q5. What is the difference between COUNT and DISTINCTCOUNT?

COUNT counts populated values, including repeated values. DISTINCTCOUNT counts unique values, so repeated values are counted once.

Q6. What is the difference between COUNT and COUNTROWS?

COUNT counts nonblank values in a supported column, while COUNTROWS counts rows in a table or table expression.

Q7. How do you count unique customers in Power BI?

Use a measure such as Unique Customers = DISTINCTCOUNT(Sales[Customer ID]).

Q8. Do measures respond to slicers?

Measures are evaluated in the current filter context and generally respond to slicers when the model relationships and visual interactions support the intended filtering.

Q9. Why might an average be different from an expected value?

Possible reasons include blank values, zeros, outliers, filters, or using the wrong aggregation level for the business question.

Q10. Why are distinct counts not always additive?

The same value may occur in multiple groups. Each group counts it once, while the overall total counts it once across the combined context.

Q11. Can SUM add the result of a calculation on each row?

SUM accepts a column reference. For a row-by-row expression that must then be added, use SUMX with a table and expression.

Q12. What should you check before using an aggregation function?

Check the data type, table grain, blank values, duplicates, filter context, and the business definition of the metric.

17. Quick Revision Checklist

• SUM adds numeric column values.
• AVERAGE calculates the arithmetic mean of numeric values.
• COUNT counts nonblank values in a supported column.
• COUNTROWS counts table rows.
• DISTINCTCOUNT counts unique values and includes blank when present.
• DISTINCTCOUNTNOBLANK excludes blank values.
• Measures are evaluated in filter context.
• Distinct counts and averages are not always additive across groups.
• Data types and missing values affect the interpretation of results.
• Always validate the metric against the business requirement.

18. Conclusion

SUM, AVERAGE, COUNT, and DISTINCTCOUNT are fundamental DAX functions for analyzing data in Power BI. SUM calculates totals, AVERAGE finds mean values, COUNT counts populated values in supported columns, and DISTINCTCOUNT measures uniqueness.

Using these functions in measures enables interactive analysis across products, customers, regions, and dates. The most important step is choosing the correct function for the question, understanding the table's grain, checking missing or duplicate values, and validating the results under different filter contexts.

Explore the course: Microsoft Power BI Training at JustAcademy

Register for a demo: JustAcademy Course Demo Registration

Visit JustAcademy: https://www.justacademy.co/